Skip to main content

DynamoDB

Overview​

DynamoDB is a fully managed NoSQL database running within AWS. For more details see https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Introduction.html.

Qarbine can query DynamoDB in 2 forms:

  1. native DynamoDB JSON specification or
  1. PartiQL (SQL’ish).

See https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html, https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.select.html
and https://partiql.org/ for information on PartiQL query language.

Further query interaction information can be found by navigating to the Tools tab

  

And ten, selecting the categories shown below.

  

Sample Data and Interactions​

This data can also be viewed at the Qarbine sandbox at nosqlreports.com. The Qarbine sample movies data and loading utility files are in the Qarbine host’s installed folder

~/qarbine.service/sample/aws/dynamodb/

The Qarbine administrator can SSH into the Qarbine host to load the movies data into a DynamoDB instance by adjusting the credentials in moviesCreateAndLoad.js. Once network connectivity and access has been verified for your environment and policies, the sample data is loaded by running

node moviesCreateAndLoad.js

Sample DynamoDB components can be found in the Qarbine catalog in the folder

example\aws\dynamodb

Below is a sample JSON specification query.

{ "TableName" : "Movies",
    ProjectionExpression: "#yr, title, info.rating",
    FilterExpression: "#info.#rating = :rating ",
    ExpressionAttributeNames: {
        "#yr": "year"
,        "#info": "info", "#rating" : "rating"
    },
    ExpressionAttributeValues: {
         ":rating" : 8
    }
}

The equivalent PartiQL query is

select * from Movies
where info.rating = 8

A sample portion of an answer set is shown below.

  

A sample element is shown below.

  

Notice the ‘info’ field contains deeply nested data including 3 arrays (actors, genres, and directors). Qarbine can easily analyze this deeply nested data and format an analysis. This interaction can also be embedded into applications for a seamless end user experience. Sample output is shown below.

  

The 3 arrays, actors, genres, and directors in this case, are easily iterated through and formatted. This result can then be exported into various popular formats and easily shared within leading collaboration tools.

Defining a Data Source​

Overview​

A Data Source is a Qarbine component responsible for retrieving data from somewhere. At a high level it has a name, a description and some arbitrary query string which when sent to the associated Qarbine Data Service endpoint returns some data. The overall execution flow for an analysis, including the optional prompt component, is shown below.

  

A single data source can be referenced by name from multiple Qarbine template components. This enables a single point of change when perhaps, an index is added, or some other query tweak is necessary. The alternative is to attempt to find all templates impacted by a schema or index change for example. This component reusability is especially beneficial when team members have varying roles and skills.

Query Language​

Either the JSON specification or the simpler PartiQL syntax may be used when defining Data Sources. Below is an example of a data source named “Top movies in 2004” using PartiQL.

  

Recall the deeply nested Movie element structure as shown below.

  

Qarbine provides several ‘pragmas’ to manipulate answer sets. Shown below is a query using the ‘pullFieldsUp’ pragma on this answer set.

  

Notice all of the fields in ‘info’ are now at the topmost level. This can make reviewing answer sets and creating analytic templates much easier.

  

Managing Answer Set Size​

The default maximum number of rows starts off at 25 for a new data source. This is useful to evolve a query from a concept to one that you have verified returns the desired answer set. As noted, any native way of limiting an answer set size is the preferred approach. This setting is in the component dialog as shown below and also accessible by clicking the ‘Gear’ icon.

  

Once you are done drafting you can adjust this parameter. A “0” indicates there is no maximum. A number greater than 0 indicates to limit the final answer set size to that number of rows. This answer set truncation comes after any native query limit. So, if the answer set from the data endpoint is quite large, that content has to be returned to the Qarbine host. It then may truncate the number of rows. It is best to truncate at the query level (i.e., use a limit) to reduce the content sent from the data endpoint to the Qarbine host in the first place.

Adjusting the Maximum Rows​

Recall the default maximum rows at the component level is 25. When you are satisfied with your query you can change that setting by clicking.

  

Adjust the setting to “0” indicating no Qarbine answer set truncation.

  

Click

  

Saving Your Component​

This can be saved in the catalog as a data source named “Top movies in 2004 with pragma”.

Click

  

Navigate to the target catalog folder within the dialog.

Fill in the name, description and other properties as desired.

Click

  

Defining an Analysis Template​

Overview​

A template defines how to process the data being retrieved from Data Source queries and other data expressions. It also defines formulas, formatting options, and other analysis and presentation options. The overall execution flow for an analysis, including the optional prompt component, is shown below

  

In this example we will discuss how the output below was obtained through a Qarbine template.

  

Main Properties​

Open the Template Designer.

First, associate the Data Source with the Template.
Click   .
Enter a name

  

Choose the data source by clicking the recents button

  

Select the component

  

Click

  

The result is

  

Toward the bottom name the main data variable as shown below.

  

This makes it easier to understand formulas especially when there are deeply nested data structures.

Close the property dialog by clicking

  

The right hand side of the Template Designer will show any metadata about the data source data. (There must be no cell chosen in the grid area for this to appear). In this case it shows the movie element structure from the main data source above.

  

Using Multiple Groups​

The overall structure of the template is shown below.

  

The 1.1 group header displays the non-array fields of the movie. The formulas refer to the ‘movie’ variable using the ‘@variable’ syntax.

The poster image is shown using an image custom cell.

  

  

Its formula is shown below.

  

A sample value is "http://ia.media-imdb.com/images/M/MV5BMTYwMjExNjc3._V1_SX400_.jpg". The makeHttps() function adjusts the HTTP URL to be an HTTPS one.

The movie counter to the left of the title is defined as

counter =if (@counter, @counter + 1, 1)

The movie’s array fields for the genres, actors, and directors are each iterated through using groups. Clicking where there is no cell and choosing the drop down option on the right hand side shows the overall retrieval and iteration loops.

  

To see the properties of group 1.1 right click on the line

  

and choose

  

The dialog shows

  

  

Recall the main source set the ‘movie’ variable and here we obtain the genres array to iterate through within group 1.1. For clarity we set the element as a variable. It can be referenced within a formula via ‘@genre’. The ‘@’ prefix refers to a variable while the ‘#’ indicates a field of the active element. The active element varies based on which data list is active and where you are within the template.

Group 1.2 is structured similarly as

  

Group 1.3 has the same pattern,

  

The use of element variables is optional. When not used the current loop element can be referenced using @current.

Increasing Component Flexibility​

Data Source​

The original data source had hard coded the year 2004. We can define a new data source which uses a variable rather than that hard coded value.

Load the previous data source and adjust the query as shown below.

select * from Movies
where year = @year and info.rating between 8 and 10

Click    (Save as) and give it the name “Top movies in @year”.

Template​

Load the “Top movies in 2004” template.

Click    (Save as) and give it the name “Top movies in @year”.

Click   to open the properties dialog.

Click    as noted below to choose a recent data source.

  

Select the row highlighted below.

  

Click

  

The dialog now shows

  

To close the dialog click

  

Save the updated template by clicking

  (save).

Prompt Integration​

Overview​

Qarbine prompts provide a way to obtain runtime values and variables for data source and template execution. To avoid hardcoding, prompts can use macro formulas to run queries which populate list widgets. Prompts are defined in a no code manner using the Prompt Designer. Shown below is the execution flow when there is a Prompt component.

  

The Prompt Designer supports a large variety of input widgets including entry fields, check boxes, radio button groups, sliders, and file input.

Example​

One possible use would be to prompt for which year to use for the above report to run. A sample is shown below.

  

Open the Prompt Designer. This can be used to prompt for multiple values. The Prompt Designer provides a large variety of widgets to choose from in a no-code fashion to create dialog prompts. This Prompt has 2 elements (a heading and a slider) as shown below.

  

The high level properties of the heading are shown below.

  

Notice the image URL can be a macro language expression and not just a simple string. The high level properties of the slider are shown below.

  

  

This prompt element sets a value for the ‘year’ variable.

To save this Prompt component click

  

and give it the name “Prompt for @year”.

Creating a Dynamic Template​

Go back to the “Top movies in @year” template. If necessary, open the Template Designer on that recent component.

Click   to open the properties dialog.

Click the 2nd tab as shown below.

  

In the drop down choose the option shown below and then click the far right recents icon.

  

Choose the recently saved prompt.

  

To close the dialog click

  

The dialog now shows

  

To close the dialog click

  

Adjust the report header as shown below so that it includes the year chosen.

  

Save the template by clicking

  

To run the template click

  

The prompt is presented for what year to use.

  

Click

  

The report result is shown.

  

This is a basic example of using dynamic DynamoDB querying and its nested JSON objects within an analytic reporting environment. When Qarbine is embedded into applications, the runtime ‘year’ variable could be fed from the application, rather than through a Qarbine prompt.

Sample Native DynamoDB Query​

The example above used the PartiQL query syntax as an introduction. PartiQL is a subset of SQL. To directly access DynamoDB features use its native query syntax which is structured as a JSON object. Below is an example

{ "TableName" : "Movies",
    ProjectionExpression: "#yr, title, info.rating",
    FilterExpression: "#yr > :minYear AND #info.#rating >= :rating ",
    ExpressionAttributeNames: {
        "#yr": "year"
,        "#info": "info", "#rating" : "rating"
    },
    ExpressionAttributeValues: {
         ":rating" : @minRating,
         ":minYear" : @minYear
    }
}

This uses 2 runtime variables which can be filled in via a prompt. The above is equivalent to

select year, title, info.rating from Movies 
where year > @minYear and info,rating >= @minRating

Sometimes the native syntax is needed though. An example is

ConsistentRead: true,

which forces strongly consistent reads vs eventually consistent ones.

PartiQL vs SQL​

Another SQL difference example is

select year, title, info from Movies 
where year = 2012 and info.rating = 8
limit 20

which results in

Unsupported clause: LIMIT at 3:7:2

For complete details on the DynamoDB query structure argument see
https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/client/dynamodb/command/QueryCommand/

Next Steps​

Querying Your Database​

For database specific interaction guides navigate to
http://doc.qarbine.com/docs/category/data-source-designer

Accessing Your Database​

To configure access to your DynamoDB database see the guides at
http://doc.qarbine.com/docs/category/data-service-configuration

References​

More information on DynamoDB and PartiQL can be found at the links below.
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Introduction.html.
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html